﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Interfaces.CommonData;
using System.Data.SqlClient;

namespace StockInfoDataLayer
{
    public class StockTable
    {
        public  List<Stock> GetIndexStocks()
        {
            DBManager mgr = DBManager.GetInstance();

            List<Stock> lstStocks = new List<Stock>();
            string sqlSelect = "SELECT ID, Ticker, [Company name] FROM StockTable";

            using (var conn = mgr.CreateConnection())
            {
                conn.Open();
                SqlCommand cmdSelect = new SqlCommand();
                cmdSelect.CommandText = sqlSelect;
                cmdSelect.Connection = conn;
                SqlDataReader rd = cmdSelect.ExecuteReader();

                while (rd.Read())
                {
                    Stock s = new Stock() {Id = (int) rd[0], Ticker = rd[1].ToString(), CompanyName = rd[2].ToString()};
                    lstStocks.Add(s);
                }
                rd.Close();
                conn.Close();
            }

            return lstStocks;
        }

        public void PopulateIndexStocks(List<Stock> stocks)
        {
            DBManager mgr = DBManager.GetInstance();
            string sqlInsert = "INSERT INTO StockTable VALUES({0},'{1}','{2}')";
            using (var conn = mgr.CreateConnection())
            {
                conn.Open();

                SqlCommand cmdDelete = new SqlCommand();
                cmdDelete.CommandText = "DELETE FROM StockTable";
                cmdDelete.Connection = conn;
                cmdDelete.ExecuteNonQuery();
                
                SqlCommand cmdInsert = new SqlCommand();
                cmdInsert.Connection = conn;

                stocks.ForEach(stock =>
                {
                    cmdInsert.CommandText = string.Format(sqlInsert, stock.Id, stock.Ticker, stock.CompanyName);
                    cmdInsert.ExecuteNonQuery();
                });
                conn.Close();
            }


        }



    }
}
